{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pymysql\n",
    "import pandas as pd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = pymysql.connect(\n",
    "    host='0.0.0.0',\n",
    "    port=55000,\n",
    "    user='root',\n",
    "    password='mysqlpw',\n",
    "    database='ddb'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 44/44 [00:19<00:00,  2.44it/s]\n",
      "100%|██████████| 51/51 [00:16<00:00,  3.15it/s]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "VAR = []\n",
    "\n",
    "for table in [\"D_S\", \"D_M\"]:\n",
    "    SQL = f\"\"\"\n",
    "    SHOW FULL COLUMNS FROM {table}\n",
    "    \"\"\"\n",
    "\n",
    "    cols = pd.read_sql(SQL, con)\n",
    "    cols = [icol for icol in cols[\"Field\"] if \"@\" in icol]\n",
    "\n",
    "    for icol in tqdm(cols[:]):\n",
    "        SQL = f\"\"\"\n",
    "        SELECT\n",
    "            TA.StationID,\n",
    "            TA.X AS X,\n",
    "            TB.T AS Y\n",
    "        FROM\n",
    "        (\n",
    "            SELECT\n",
    "                StationID, STD(`{icol}`) AS X\n",
    "            FROM {table}\n",
    "            GROUP BY StationID\n",
    "        ) AS TA\n",
    "        LEFT JOIN (\n",
    "            SELECT\n",
    "                StationID, 2 * ASIN(\n",
    "                    SQRT(\n",
    "                        SIN((Latitude-31.89)/2) * SIN((Latitude-31.89)/2) + \n",
    "                        COS(Latitude) * COS(31.89) * SIN((Longitude-104.46)/2) * SIN((Longitude-104.46)/2)\n",
    "                    )\n",
    "                ) * 6371 AS T\n",
    "            FROM D_SInfo\n",
    "            ORDER BY T\n",
    "        ) AS TB\n",
    "        ON TA.StationID = TB.StationID\n",
    "        \"\"\"\n",
    "\n",
    "        data = pd.read_sql(SQL, con)\n",
    "        VAR.append([icol, abs(data.corr()[\"X\"][\"Y\"])])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VAR</th>\n",
       "      <th>CORR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>magn@kurt</td>\n",
       "      <td>0.198905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>magn@skew</td>\n",
       "      <td>0.197935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>magn@ulf_kurt</td>\n",
       "      <td>0.195142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>magn@level5d_abs_mean</td>\n",
       "      <td>0.167132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>magn@power_40_60</td>\n",
       "      <td>0.162849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>magn@level6d_energy</td>\n",
       "      <td>0.004320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>magn@ulf_abs_max</td>\n",
       "      <td>0.003736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>magn@power_30_35</td>\n",
       "      <td>0.002270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>magn@ulf_abs_mean</td>\n",
       "      <td>0.000938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>magn@power_15_20</td>\n",
       "      <td>0.000728</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      VAR      CORR\n",
       "47              magn@kurt  0.198905\n",
       "46              magn@skew  0.197935\n",
       "89          magn@ulf_kurt  0.195142\n",
       "73  magn@level5d_abs_mean  0.167132\n",
       "62       magn@power_40_60  0.162849\n",
       "..                    ...       ...\n",
       "82    magn@level6d_energy  0.004320\n",
       "90       magn@ulf_abs_max  0.003736\n",
       "60       magn@power_30_35  0.002270\n",
       "85      magn@ulf_abs_mean  0.000938\n",
       "57       magn@power_15_20  0.000728\n",
       "\n",
       "[95 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(VAR, columns=[\"VAR\", \"CORR\"]).sort_values(\"CORR\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
